So I am building some reports where there are all kinds of variations of numbers.  Some are billions of dollars, some are hundreds of people, some are dozens of degrees, etc.  The format is always the same, a single chart and a single table underneath.  So an obvious case for reuse.  Just refresh the page with each filter applied of what they want to see.

Now a table with numbers looks good when they are small.

But starts looking pretty bad when trying to do billions of dollars with a lot more years than this quick example.

This looks much better.

(Note: In Billions of dollars)

So, how do you round to billions, and other large numbers?  You may already know but the day I ran into this I was burnt out and I just couldn’t get it.  The Round() function is only going to work on decimals. Is there a parameter to Round() that I just don’t remember?  Do I try List functions with the comma as the delimiter and then drop what I don’t need?  Do I cheat and just put the number in the database already formatted how I want?  That would work, but what if someone else wants to use this same number in another report and they want the whole number.  Well Google is no help on this and Stack Overflow is also a dead end.  This is the part where you think: “Just do this {answer here} and you are done.  Why are  you making it so hard?”

So I’ll digress a bit here.  This is a scenario that once I got the answer I chastised myself for not seeing how simple it actually was.  Then I realized it was also something others maybe ran into and figured out.  They just never bothered to put the answer out there because they thought they were the only ones.  That it was too simple to be worth explaining.  This goes for a lot of questions out there because the answer is usually simpler than one thought once they have it.  I’m at the point now in my career that I have no problems putting it out there that here’s a time I got caught and felt stupid.  Maybe this way that next person who gets stuck has an answer that shows up in Google and saves them some time.  Hopefully others will do the same on another “too easy” answer.  Or just send it to me and I’ll put it up and take the “hit” on not knowing something.  Another digression, there isn’t really a “hit” to take. it just feels like there is cause its a bit embarrassing sometimes to miss the obvious.

So what was the answer.  Its still to use Round(), but the trick is to get the number down to where you need it.  If you want to do like the example above and get 2,022,000,000 down to 2.022 just divide by 1,000,000,000 and then round.  And you are done.  If it’s millions, divide by 1,000,000.  Simple and the kind of problem you kick yourself for when you don’t get.  There may be an even simpler solution that shows up in the comments below.

Now, I will take it a bit further and detail some of how I structured the code so I would know when I needed to do this division/rounding.  In my database I made a definition table that told me about the numbers I was reporting.  Yes, overkill for just this rounding but I am doing a lot more based on the numbers: graph display changes, what color in the graph does each series get, axis titles, etc. etc.  The indicator on how to round is just another column where I specify a number is millions, billions, simple number, one decimal place, two decimals, etc.  In the code of the page I have a switch statement that looks at the definition column and formats the number appropriately.

Last wrinkle I had is also a tip worth sharing.  They wanted only one decimal place with millions but three with billions.  If you use use NumberFormat() with the correct mask it also will round the number for you.  After you do the division of course.

So now its out there, documentation of a simple answer to a simple problem and proof that someone else got stuck on something “simple”.